package com.house.java.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.house.java.dao.UserDAO;
import com.house.java.model.Paging;
import com.house.java.model.User;
import com.house.java.util.DBUtil;
import com.house.java.util.StringUtils;

public class UserDaoImpl implements UserDAO{
	
	private static Logger log = Logger.getLogger(UserDaoImpl.class);

	public void addUser(User user) {
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "insert into user(" +
			"USER_NAME,PWD,TRUE_NAME,TELEPHONE,USER_TYPE," +
			"NEW_HOUSE_AUTH,SECOND_HOUSE_AUTH,NEWS_AUTH,COUNTRY_CODE,PROVINCE_CODE," +
			"CITY_CODE)" +
			" values(?,?,?,?,? ,?,?,?,?,? ,?)";
			PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			int parameterIndex = 1;
			
			pst.setString(parameterIndex++, user.getUserName());
			pst.setString(parameterIndex++, user.getPassword());
			pst.setString(parameterIndex++, user.getTrueName());
			pst.setString(parameterIndex++, user.getTelephone());
			pst.setInt(parameterIndex++, user.getUserType());
			pst.setInt(parameterIndex++, user.getNewHouseAuth());
			pst.setInt(parameterIndex++, user.getSecondHouseAuth());
			pst.setInt(parameterIndex++, user.getNewsAuth());
			pst.setInt(parameterIndex++, user.getCountryCode());
			pst.setInt(parameterIndex++, user.getProvinceCode());
			pst.setInt(parameterIndex++, user.getCityCode());
			pst.setInt(parameterIndex++, user.getUserType());
			
			log.error("parameterIndex ---> "+parameterIndex);
			pst.executeUpdate();
			// 获取刚刚插入的记录的主键，设置到house对象中
			ResultSet resultSet = pst.getGeneratedKeys();
			if(resultSet.next()){
				Long id = resultSet.getLong(1);
				user.setId(id);
			}
			resultSet.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		
	}

	public List<User> queryUserByUserName(Long id, String userName,
			Paging paging) {
		List<User> list = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from user where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from user where 1=1 ");
		int index = 0;
		if(null != id){
			sb.append(" and id=? ");
			sbCount.append(" and id=? ");
			index++;
		}
		if(StringUtils.hasLength(userName)){
			sb.append(" and USER_NAME=? ");
			sbCount.append(" and USER_NAME=? ");
			index++;
		}
		if(paging != null){
			sb.append(" limit ?,?");
			index++;
			index++;
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(index > 0){
				int paramsIndex = 1;
				if(null != id){
					pst.setLong(paramsIndex, id);
					pstCount.setLong(paramsIndex, id);
					paramsIndex++;
				}
				if(null != userName){
					pst.setString(paramsIndex, userName);
					pstCount.setString(paramsIndex, userName);
					paramsIndex++;
				}
				if(paging != null){
					pst.setInt(paramsIndex++, (paging.getCurrentPage()-1)*paging.getPageSize());
					pst.setInt(paramsIndex++, paging.getPageSize());
					buildPaging(pstCount, paging);
				}
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			User user;
			while(result.next()){
				user = new User();
				user.setId(result.getLong("ID"));
				user.setCityCode(result.getInt("CITY_CODE"));
				user.setCountryCode(result.getInt("COUNTRY_CODE"));
				user.setNewHouseAuth(result.getInt("NEW_HOUSE_AUTH"));
				user.setNewsAuth(result.getInt("NEWS_AUTH"));
				user.setPassword(result.getString("PWD"));
				user.setProvinceCode(result.getInt("PROVINCE_CODE"));
				user.setSecondHouseAuth(result.getInt("SECOND_HOUSE_AUTH"));
				user.setTelephone(result.getString("TELEPHONE"));
				user.setTrueName(result.getString("TRUE_NAME"));
				user.setUserType(result.getInt("USER_TYPE"));
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return list;
	}
	
	public int delete(Long id){
		int result = -1;
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "delete from user where id = ?";
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setLong(parameterIndex, id);
			// 获取刚刚插入的记录的主键，设置到house对象中
			result = pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		return result;
	}
	
	
	private static void logConnectionCloseExceptionFunction(){
		log.error(Thread.currentThread().getId()+" close Connection appear an Exception");
	}
	private static void logConnectionRollbackException(){
		log.error(Thread.currentThread().getId()+" rollback Connection appear an Exception");
	}

	/**
	 * 适用于  select count(*) 形式的SQL
	 * @param pst
	 * @param paging
	 */
	private void buildPaging(PreparedStatement pst,Paging paging){
		try {
			ResultSet rs = pst.executeQuery();
			paging.setTotalSize(rs.next()?rs.getInt(1):0);
			log.info(pst.toString());
			rs.close();
			pst.close();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}
}
